"""
Case Type   : 基础功能-SQL语法-DDL-ALTER TABLE PARTITION
Case Name   : Interval分区表执行truncate partition,并行对该分区copy
Create At   : 2022/05/20
Owner       : kyeleze
Description :
    1.创建interval分区表并插入数据
    2.建立db1,开始事务truncate partition;建立db2,启动线程开始事务同分区copy
    3.查看第二个事务状态
    4.db1执行rollback
Expect      :
    1.成功
    2.成功
    3.第二个事务状态为active
    4.成功
History     :
"""

import os
import unittest
import time

import py_opengauss
from yat.test import Node
from yat.test import macro

from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.ComThread import ComThread


class IntervalPartitionCase(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.user_node = Node('PrimaryDbUser')
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.table_name = 't_interval_table_0142'
        self.file_name = 'f_interval_table_0142.dat'
        self.copy_dir_path = os.path.join(macro.DB_INSTANCE_PATH,
                                          'pg_copydir')
        self.user_param = f'-U {self.user_node.db_user} ' \
                          f'-W {self.user_node.db_password}'
        self.con_info = f'opengauss://' \
                        f'{self.user_node.db_user}:' \
                        f'{self.user_node.db_password}@' \
                        f'{self.user_node.db_host}:' \
                        f'{self.user_node.db_port}/' \
                        f'{self.user_node.db_name}'

    def test_partition_ddl_dml(self):
        step = '----step1:创建interval分区表并插入数据;expect:成功----'
        self.log.info(step)
        sql_cmd = f'''drop table if exists {self.table_name};
        create table {self.table_name}
        (prod_id NUMBER(6),
         time_id DATE,
         channel_id CHAR(10))
        partition by range (time_id)
        interval('1 day')
        ( partition p1 values less than ('2019-02-01 00:00:00'),
          partition p2 values less than ('2019-02-02 00:00:00'));
        insert into {self.table_name} 
        values(1, '2019-01-31 00:00:00', 'a'),
        (2, '2019-02-1 00:00:00', 'b');
        '''
        result = self.primary_sh.execut_db_sql(sql_cmd,
                                               sql_type=self.user_param)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS,
                      result, "执行失败:" + step)
        self.assertIn(self.constant.INSERT_SUCCESS_MSG, result,
                      "执行失败:" + step)

        text = '--构造数据文件--'
        self.log.info(text)
        exec_cmd = f'mkdir {self.copy_dir_path};' \
                   f'touch {os.path.join(self.copy_dir_path, self.file_name)};'
        self.log.info(exec_cmd)
        result = self.user_node.sh(exec_cmd).result()
        self.log.info(result)
        self.assertNotIn(self.constant.SQL_WRONG_MSG[1], result)
        sql_cmd = f"copy (select * from {self.table_name} partition(p1)) to " \
                  f"'{os.path.join(self.copy_dir_path, self.file_name)}';"
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('COPY', result, '执行失败:' + text)

        step = '--step2:建立db1,开始事务truncate partition;' \
               '启动线程开始事务同分区copy' \
               'expect:成功--'
        self.log.info(step)
        self.db1 = py_opengauss.open(self.con_info)
        begin_result = self.db1.query('begin;')
        self.log.info(begin_result)
        self.assertIn('BEGIN', begin_result, "执行失败:" + step)
        ddl_sql = f'alter table {self.table_name} truncate partition p1;'
        result = self.db1.query(ddl_sql)
        self.log.info(result)
        self.assertIn(self.constant.ALTER_TABLE_MSG, result, "执行失败:" + step)
        time.sleep(1)
        dml_sql = f"begin;" \
                  f"copy {self.table_name} from " \
                  f"'{os.path.join(self.copy_dir_path, self.file_name)}';" \
                  f"commit;"
        thread = ComThread(self.primary_sh.execut_db_sql, args=(dml_sql,))
        thread.setDaemon(True)
        thread.start()

        step = '--step3:查看第二个事务状态 expect:第二个事务处于active状态--'
        self.log.info(step)
        time.sleep(1)
        check_sql = f"select state from pg_stat_activity where query like " \
                    f"'begin;copy {self.table_name}%';"
        result = self.primary_sh.execut_db_sql(check_sql)
        self.log.info(result)
        self.assertIn('active', result, "执行失败:" + step)

        step = '--step4:db1执行rollback expect:成功'
        self.log.info(step)
        result = self.db1.query('rollback')
        self.log.info(result)
        self.assertIn(self.constant.ROLLBACK_MSG, result, "执行失败:" + step)

    def tearDown(self):
        step = '--恢复环境,关闭session,删除测试表--'
        self.log.info(step)
        self.db1.close()
        result = self.primary_sh.execut_db_sql(f'drop table {self.table_name};')
        self.log.info(result)
        exec_cmd = f'''rm -rf {self.copy_dir_path}'''
        self.log.info(exec_cmd)
        msg = self.user_node.sh(exec_cmd).result()
        self.log.info(msg)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      result, "执行失败:" + step)
        self.assertEqual("", msg, "执行失败:" + step)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')

